﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

namespace LibraryUtility.Data
{
    /// <summary>
    /// ClassName:DataAccess Data Base operation .
    /// Auther:Leo.wl
    /// Time:2010-04-15
    /// </summary>
    public sealed class DataAccess
    {

        #region Connection
        /// <summary>
        /// Gets the connection string.
        /// </summary>
        /// <value>The connection string.</value>
        private static string ConnectionString
        {
            [DebuggerStepThrough()]
            get
            {
                return ConfigurationManager.ConnectionStrings["ConnectionSQLStr"].ConnectionString;
            }
        }
        /// <summary>
        /// Initializes a new instance of the <see cref="DataAccess"/> class.
        /// </summary>
        private DataAccess()
        {
            ;
        }

        /// <summary>
        /// Gets the DB connection.
        /// </summary>
        /// <value>The DB connection.</value>
        private static SqlConnection DBConnection
        {
            [DebuggerStepThrough()]
            get
            {
                SqlConnection conn = null;
                try
                {
                    conn = new SqlConnection(ConnectionString);
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                return conn;
            }
        }
        #endregion

        #region ExcuteQuery
        /// <summary>
        /// Excutes the query.
        /// </summary>
        /// <param name="sql">The SQL.</param>
        /// <returns></returns>
        [DebuggerStepThrough()]
        public static SqlDataReader ExcuteQuery(string sql)
        {
            SqlCommand cmd = new SqlCommand(sql);
            return ExcuteQuery(cmd);
        }

        /// <summary>
        /// Excutes the query.
        /// </summary>
        /// <param name="cmd">The CMD.</param>
        /// <returns></returns>
        [DebuggerStepThrough()]
        public static SqlDataReader ExcuteQuery(SqlCommand cmd)
        {
            SqlConnection cnn = null;
            try
            {
                cnn = DataAccess.DBConnection;
                cnn.Open();
                return ExcuteQuery(cnn, cmd);
            }
            catch
            {
                if (cnn != null)
                {
                    cnn.Close();
                    cnn.Dispose();
                }
                throw;
            }
        }

        /// <summary>
        /// Excutes the query.
        /// </summary>
        /// <param name="cnn">The CNN.</param>
        /// <param name="cmd">The CMD.</param>
        /// <returns></returns>
        [DebuggerStepThrough()]
        private static SqlDataReader ExcuteQuery(SqlConnection cnn, SqlCommand cmd)
        {
            if (cmd.Connection != null)
                throw new Exception();

            SqlDataReader dr = null;
            try
            {
                cmd.Connection = cnn;
                dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return dr;
            }
            catch
            {
                if (dr != null)
                {
                    dr.Close();
                }
                throw;
            }
        }
        #endregion

        #region ExcuteCmd
        /// <summary>
        /// Excutes the CMD.
        /// </summary>
        /// <param name="sql">The SQL.</param>
        /// <returns></returns>
        [DebuggerStepThrough()]
        public static int ExcuteCmd(string sql)
        {
            SqlCommand cmd = new SqlCommand(sql);
            return ExcuteCmd(cmd);
        }

        /// <summary>
        /// Excutes the CMD.
        /// </summary>
        /// <param name="cmd">The CMD.</param>
        /// <returns></returns>
        [DebuggerStepThrough()]
        public static int ExcuteCmd(SqlCommand cmd)
        {
            SqlConnection cnn = null;
            try
            {
                cnn = DataAccess.DBConnection;
                cnn.Open();
                return ExcuteCmd(cnn, cmd);
            }
            catch
            {
                throw;
            }
            finally
            {
                if (cnn != null)
                {
                    cnn.Close();
                    cnn.Dispose();
                }
            }
        }

        /// <summary>
        /// Excutes the CMD.
        /// </summary>
        /// <param name="cnn">The CNN.</param>
        /// <param name="cmd">The CMD.</param>
        /// <returns></returns>
        [DebuggerStepThrough()]
        private static int ExcuteCmd(SqlConnection cnn, SqlCommand cmd)
        {
            if (cmd.Connection != null)
                throw new Exception();

            try
            {
                cmd.Connection = cnn;
                return cmd.ExecuteNonQuery();
            }
            catch
            {
                throw;
            }
            finally
            {
                cnn.Close();
                cnn.Dispose();
            }
        }
        #endregion

        #region Page DateSet
        /// <summary>
        /// Gets the page count.
        /// </summary>
        /// <param name="sql">The SQL.</param>
        /// <returns></returns>
        [DebuggerStepThrough()]
        public static int GetPageCount(string sql)
        {
            DataSet ds = null;
            int str_recordcount = 0;
            SqlConnection cnn = null;
            SqlDataAdapter da = null;
            try
            {
                ds = new DataSet();
                cnn = DBConnection;
                cnn.Open();
                da = new SqlDataAdapter(sql, cnn);
                da.Fill(ds, "a");
                str_recordcount = ds.Tables["a"].Rows.Count;
            }
            catch
            {
                ;
            }
            finally
            {
                if (ds != null)
                    ds.Dispose();
                if (da != null)
                    da.Dispose();
                if (cnn != null)
                {
                    cnn.Close();
                }


            }
            return str_recordcount;
        }
        /// <summary>
        /// Records the count.
        /// </summary>
        /// <param name="countCmd">The count CMD.</param>
        /// <returns></returns>
        [DebuggerStepThrough()]
        public static int RecordCount(string countCmd) // Get Count of Records
        {
            int recCount = 0;  // Return Value 
            SqlConnection cnn = null;
            SqlCommand cmd = null;
            try
            {
                cnn = DBConnection;
                cnn.Open();

                cmd = new SqlCommand(countCmd, cnn);
                //cmd.Connection.Open();
                recCount = (int)cmd.ExecuteScalar();
            } // end try
            finally
            {
                if (cmd != null)
                    cmd.Dispose();

                if (cnn != null)
                {
                    cnn.Close();
                    cnn.Dispose();
                }
            }
            return recCount;
        }
        /// <summary>
        /// Gets the data set.
        /// </summary>
        /// <param name="selectCmd">The select CMD.</param>
        /// <param name="Table0">The table0.</param>
        /// <returns></returns>
        [DebuggerStepThrough()]
        public static DataSet GetDataSet(string selectCmd, string Table0) // Select From Table
        {
            DataSet ds = null;
            SqlConnection cnn = null;
            SqlDataAdapter sda = null;
            try
            {
                cnn = DBConnection;
                cnn.Open();

                sda = new SqlDataAdapter(selectCmd, cnn);
                ds = new DataSet();
                sda.Fill(ds, Table0);
            }
            finally
            {
                if (sda != null)
                    sda.Dispose();
                if (ds != null)
                    ds.Dispose();
                if (cnn != null)
                {
                    cnn.Close();
                    cnn.Dispose();
                }
            }
            return ds;
        }
        /// <summary>
        /// Gets the page data set.
        /// </summary>
        /// <param name="PageSize">Size of the page.</param>
        /// <param name="CurrentPageIndex">Index of the current page.</param>
        /// <param name="sql">The SQL.</param>
        /// <param name="Table0">The table0.</param>
        /// <returns></returns>
        [DebuggerStepThrough()]
        public static DataSet GetPageDataSet(int PageSize, int CurrentPageIndex, string sql, string Table0)
        {
            DataSet ds = null;
            SqlConnection cnn = null;
            SqlDataAdapter da = null;
            try
            {
                ds = new DataSet();
                cnn = DBConnection;
                cnn.Open();

                da = new SqlDataAdapter(sql, cnn);
                da.Fill(ds, PageSize * (CurrentPageIndex - 1), PageSize, Table0);
            }
            catch
            {
                ;
            }
            finally
            {
                if (ds != null)
                    ds.Dispose();
                if (da != null)
                    da.Dispose();
                if (cnn != null)
                    cnn.Close();

            }
            return ds;
        }
        #endregion

        #region ExecuteScalar
        /// <summary>
        /// Executes the scalar.
        /// </summary>
        /// <param name="sql">The SQL.</param>
        /// <returns></returns>
        [DebuggerStepThrough()]
        public static object ExecuteScalar(string sql)
        {
            SqlCommand cmd = new SqlCommand(sql);
            return ExecuteScalar(cmd);
        }

        /// <summary>
        /// Executes the scalar.
        /// </summary>
        /// <param name="cmd">The CMD.</param>
        /// <returns></returns>
        [DebuggerStepThrough()]
        public static object ExecuteScalar(SqlCommand cmd)
        {
            SqlConnection cnn = null;
            try
            {
                cnn = DataAccess.DBConnection;
                cnn.Open();

                return ExecuteScalar(cnn, cmd);
            }
            catch
            {
                throw;
            }
            finally
            {
                if (cnn != null)
                {
                    cnn.Close();
                    cnn.Dispose();
                }
            }
        }

        /// <summary>
        /// Executes the scalar.
        /// </summary>
        /// <param name="cnn">The CNN.</param>
        /// <param name="cmd">The CMD.</param>
        /// <returns></returns>
        [DebuggerStepThrough()]
        private static object ExecuteScalar(SqlConnection cnn, SqlCommand cmd)
        {
            if (cmd.Connection != null)
                throw new Exception();

            try
            {
                cmd.Connection = cnn;
                return cmd.ExecuteScalar();
            }
            catch
            {
                throw;
            }
        }
        #endregion

        #region Procedure
        /// <summary>
        /// Runs the proc.
        /// </summary>
        /// <param name="ProcName">Name of the proc.</param>
        /// <param name="paras">The paras.</param>
        /// <returns></returns>
        public static bool RunProc(string ProcName, SqlParameter[] paras)
        {
            SqlConnection cnn = null;
            SqlCommand cmd = null;

            try
            {
                cnn = DBConnection;
                cnn.Open();

                cmd = new SqlCommand(ProcName, cnn);
                cmd.CommandType = CommandType.StoredProcedure;
                foreach (SqlParameter p in paras)
                    cmd.Parameters.Add(p);
                cmd.ExecuteNonQuery();
            }
            catch
            {
                throw;
            }
            finally
            {
                if (cmd != null)
                    cmd.Dispose();
                if (cnn != null)
                {
                    cnn.Close();
                    cnn.Dispose();
                }
            }
            return true;
        }
        #endregion

        #region GetTable
        /// <summary>
        /// Gets the table.
        /// </summary>
        /// <param name="sql">The SQL.</param>
        /// <returns></returns>
        [DebuggerStepThrough()]
        public static DataTable GetTable(string sql)
        {
            SqlCommand cmd = new SqlCommand(sql);
            return GetTable(cmd);
        }

        /// <summary>
        /// Gets the table.
        /// </summary>
        /// <param name="cmd">The CMD.</param>
        /// <returns></returns>
        [DebuggerStepThrough()]
        public static DataTable GetTable(SqlCommand cmd)
        {
            SqlConnection cnn = null;
            try
            {
                cnn = DataAccess.DBConnection;
                cnn.Open();

                return GetTable(cnn, cmd);
            }
            catch
            {
                throw;
            }
            finally
            {
                //cmd.Dispose();
                if (cnn != null)
                {
                    cnn.Close();
                    cnn.Dispose();
                }
            }
        }

        /// <summary>
        /// Gets the table.
        /// </summary>
        /// <param name="cnn">The CNN.</param>
        /// <param name="cmd">The CMD.</param>
        /// <returns></returns>
        [DebuggerStepThrough()]
        private static DataTable GetTable(SqlConnection cnn, SqlCommand cmd)
        {
            if (cmd.Connection != null)
                throw new Exception();

            DataTable dt = new DataTable();
            SqlDataAdapter da = null;
            try
            {
                cmd.Connection = cnn;
                da = new SqlDataAdapter(cmd);
                da.Fill(dt);
            }
            catch
            {
                throw;
            }
            finally
            {
                if (da != null)
                    da.Dispose();
            }
            return dt;
        }
        #endregion

        #region CreateSqlParameter
        /// <summary>
        /// Creates the SQL parameter.
        /// </summary>
        /// <param name="name">The name.</param>
        /// <param name="v">The v.</param>
        /// <param name="direction">The direction.</param>
        /// <param name="type">The type.</param>
        /// <returns></returns>
        [DebuggerStepThrough()]
        public static SqlParameter CreateSqlParameter(string name, object v, ParameterDirection direction, SqlDbType type)
        {
            return CreateSqlParameter(name, v, direction, type, 0);
        }


        /// <summary>
        /// Creates the SQL parameter.
        /// </summary>
        /// <param name="name">The name.</param>
        /// <param name="v">The v.</param>
        /// <param name="direction">The direction.</param>
        /// <param name="type">The type.</param>
        /// <param name="size">The size.</param>
        /// <returns></returns>
        [DebuggerStepThrough()]
        public static SqlParameter CreateSqlParameter(string name, object v, ParameterDirection direction, SqlDbType type, int size)
        {
            SqlParameter parm = new SqlParameter();
            parm.ParameterName = name;
            parm.Value = v;
            parm.Direction = direction;
            parm.SqlDbType = type;
            if (size > 0)
                parm.Size = size;
            return parm;
        }

        /// <summary>
        /// Creates the SQL parameter int.
        /// </summary>
        /// <param name="name">The name.</param>
        /// <param name="v">The v.</param>
        /// <returns></returns>
        [DebuggerStepThrough()]
        public static SqlParameter CreateSqlParameterInt(string name, int v)
        {
            return CreateSqlParameter(name, (object)v, ParameterDirection.Input, SqlDbType.Int, 0);
        }
        /// <summary>
        /// Creates the SQL parameter bit.
        /// </summary>
        /// <param name="name">The name.</param>
        /// <param name="v">if set to <c>true</c> [v].</param>
        /// <returns></returns>
        [DebuggerStepThrough()]
        public static SqlParameter CreateSqlParameterBit(string name, bool v)
        {
            return CreateSqlParameter(name, (object)v, ParameterDirection.Input, SqlDbType.Bit, 0);
        }

        /// <summary>
        /// Creates the SQL parameter N var char.
        /// </summary>
        /// <param name="name">The name.</param>
        /// <param name="v">The v.</param>
        /// <param name="size">The size.</param>
        /// <returns></returns>
        [DebuggerStepThrough()]
        public static SqlParameter CreateSqlParameterNVarChar(string name, string v, int size)
        {
            return CreateSqlParameter(name, (object)v, ParameterDirection.Input, SqlDbType.NVarChar, size);
        }

        /// <summary>
        /// Creates the SQL parameter var char.
        /// </summary>
        /// <param name="name">The name.</param>
        /// <param name="v">The v.</param>
        /// <param name="size">The size.</param>
        /// <returns></returns>
        [DebuggerStepThrough()]
        public static SqlParameter CreateSqlParameterVarChar(string name, string v, int size)
        {
            return CreateSqlParameter(name, (object)v, ParameterDirection.Input, SqlDbType.VarChar, size);
        }

        /// <summary>
        /// Creates the SQL parameter char.
        /// </summary>
        /// <param name="name">The name.</param>
        /// <param name="v">The v.</param>
        /// <param name="size">The size.</param>
        /// <returns></returns>
        [DebuggerStepThrough()]
        public static SqlParameter CreateSqlParameterChar(string name, string v, int size)
        {
            return CreateSqlParameter(name, (object)v, ParameterDirection.Input, SqlDbType.Char, size);
        }

        /// <summary>
        /// Creates the SQL parameter N text.
        /// </summary>
        /// <param name="name">The name.</param>
        /// <param name="v">The v.</param>
        /// <returns></returns>
        [DebuggerStepThrough()]
        public static SqlParameter CreateSqlParameterNText(string name, string v)
        {
            return CreateSqlParameter(name, (object)v, ParameterDirection.Input, SqlDbType.NText);
        }

        /// <summary>
        /// Creates the SQL parameter unique identifier.
        /// </summary>
        /// <param name="name">The name.</param>
        /// <param name="v">The v.</param>
        /// <returns></returns>
        [DebuggerStepThrough()]
        public static SqlParameter CreateSqlParameterUniqueIdentifier(string name, Guid v)
        {
            return CreateSqlParameter(name, (object)v, ParameterDirection.Input, SqlDbType.UniqueIdentifier, 0);
        }

        /// <summary>
        /// Creates the SQL parameter date time.
        /// </summary>
        /// <param name="name">The name.</param>
        /// <param name="v">The v.</param>
        /// <returns></returns>
        [DebuggerStepThrough()]
        public static SqlParameter CreateSqlParameterDateTime(string name, DateTime v)
        {
            return CreateSqlParameter(name, (object)v, ParameterDirection.Input, SqlDbType.DateTime);
        }

        /// <summary>
        /// Creates the SQL parameter output id.
        /// </summary>
        /// <param name="name">The name.</param>
        /// <returns></returns>
        [DebuggerStepThrough()]
        public static SqlParameter CreateSqlParameterOutputId(string name)
        {
            return CreateSqlParameter(name, (object)null, ParameterDirection.Output, SqlDbType.Int);
        }
        #endregion


    }
}
